package com.yang.dao;

import com.yang.pojo.Role;
import com.yang.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoleDao {
    //查询角色表的全部信息
    public ArrayList<Role> selectAllRole() throws SQLException {
        ArrayList<Role> roleList = new ArrayList<>();
        String sql = "SELECT role_id roleId,role_name roleName,role_desc roleDesc FROM tb_roles";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        roleList = (ArrayList<Role>) queryRunner.query(sql, new BeanListHandler <Role> (Role.class));
        return roleList;
    }

    //保存角色信息
    public int insertRole(String roleName,String roleDesc) throws SQLException {
        int i = 0;
        String sql = "INSERT INTO tb_roles(role_name,role_desc) VALUES(?,?)";
//        Object[] params = {role.getRoleName(),role.getRoleDesc()};
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Object object =  queryRunner.insert(sql,new ScalarHandler<>(),roleName,roleDesc);
        i = Integer.parseInt(String.valueOf(object));
        return i;
    }

    //保存用户id与菜单id之间的关系
    public int insertRoleAndMenus(int roleId,int menuId) throws SQLException {
        int i = 0;
        String sql = "INSERT INTO tb_role_menu(role_id,menu_id) VALUES (?,?)";
        Object[] params = {roleId,menuId};
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, roleId, menuId);
        return i;
    }

    //删除用户与表之间的关系
    public int deleteRoleAndMenus(int roleId) throws SQLException {
        int i = 0;
        String sql = "DELETE FROM tb_role_menu WHERE role_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, roleId);
        return i;
    }

    //删除用户信息
    public int deleteRole(int roleId) throws SQLException {
        int i = 0;
        String sql = "DELETE FROM tb_roles WHERE role_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, roleId);
        return i;
    }

    //根据角色id查询角色信息
    public Role selectRoleById(int roleId) throws SQLException {
        Role roleList = null;
        String sql = "SELECT role_id roleId,role_name roleName,role_desc roleDesc FROM tb_roles WHERE role_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        roleList = queryRunner.query(sql, new BeanHandler<Role>(Role.class), roleId);
        return roleList;
    }

    //根据角色id查询角色拥有的权限菜单的id
    public List<Integer> selectMenuIdByRoleId(int roleId) throws SQLException {
        List<Integer> menuIdList = new ArrayList<>();
        String sql = "SELECT menu_id menuId FROM tb_role_menu WHERE role_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //自定义结果集处理器
        ResultSetHandler<List<Integer>> resultSetHandler = new ResultSetHandler<List<Integer>>(){
            @Override
            public List<Integer> handle(ResultSet resultSet) throws SQLException{
                ArrayList<Integer> list = new ArrayList<>();
                while (resultSet.next()){
                    int mid = resultSet.getInt("menuId");
                    list.add(mid);
                }
                return list;
            }
        };
        menuIdList = queryRunner.query(sql, resultSetHandler, roleId);
        return menuIdList;
    }

    //根据角色id修改角色信息
    public int updateRole(String roleName,String roleDesc,int roleId) throws SQLException {
        int i = 0;
        String sql = "UPDATE tb_roles SET role_name=?,role_desc=? WHERE role_id=?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, roleName, roleDesc, roleId);
        return i;
    }



}
